package appVeterinaria.dao;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import appVeterinaria.excepcion.DAOExcepcion;
import appVeterinaria.modelo.Cita;
import appVeterinaria.util.ConexionBD;



public class CitaDAO extends BaseDAO {

	public Collection<Cita> buscarPorIdCita(String id_cita)
			throws DAOExcepcion {
		String query = "select id_cita, id_cliente, id_paciente, id_servicio, cant_servicio, fecha  from citas where id_cita like ?";
		Collection<Cita> lista = new ArrayList<Cita>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, "%" + id_cita + "%");
			rs = stmt.executeQuery();
			while (rs.next()) {
				Cita vo = new Cita();
				vo.setIdCita(rs.getString("id_cita"));
				vo.setIdCliente(rs.getString("id_cliente"));
				vo.setIdPaciente(rs.getString("id_paciente"));
				vo.setIdServicio(rs.getString("id_servicio"));
				vo.setCantServicio(rs.getString("cant_servicio"));
				vo.setFecha(rs.getString("fecha"));
				lista.add(vo);
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		System.out.println(lista.size());
		return lista;
	}

	public Cita insertar(Cita vo) throws DAOExcepcion {
		String query = "insert into citas(id_cita, id_cliente, id_paciente, id_servicio, cant_servicio, fecha) values (?,?,?,?,?,?)";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, vo.getIdCita());
			stmt.setString(2, vo.getIdCliente());
			stmt.setString(3, vo.getIdPaciente());
			stmt.setString(4, vo.getIdServicio());
			stmt.setString(5, vo.getCantServicio());
			stmt.setString(6, vo.getFecha());
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}
			// Obtener el ultimo id
			String id = "1";
			query = "select last_insert_id()";
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			if (rs.next()) {
				id = rs.getString(1);
			}
			vo.setIdCita("1");

		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	}

	public Cita obtener(String idCita) throws DAOExcepcion {
		Cita vo = new Cita();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String query = "select id_cita, id_cliente, id_paciente, id_servicio, cant_servicio, fecha  from citas where id_cita like ?";
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, idCita);
			rs = stmt.executeQuery();
			if (rs.next()) {
				vo.setIdCita(rs.getString(1));
				vo.setIdCliente(rs.getString(2));
				vo.setIdPaciente(rs.getString(3));
				vo.setIdServicio(rs.getString(4));
				vo.setCantServicio(rs.getString(5));
				vo.setFecha(rs.getString(6));
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	}

	public void eliminar(String idCita) throws DAOExcepcion {
		String query = "delete from citas WHERE id_cita=?";
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, idCita);
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo eliminar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
	}

	public Cita actualizar(Cita vo) throws DAOExcepcion {
		String query = "update citas set id_cita=?,id_paciente=? where id_cita=?";
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, vo.getIdCita());
			stmt.setString(2, vo.getIdCliente());
			stmt.setString(3, vo.getIdPaciente());
			stmt.setString(4, vo.getIdServicio());
			stmt.setString(5, vo.getCantServicio());
			stmt.setString(6, vo.getFecha());
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo actualizar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	}

	public Collection<Cita> listar() throws DAOExcepcion {
		Collection<Cita> c = new ArrayList<Cita>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			String query = "select id_cita,id_cliente,id_paciente,id_servicio,cant_servicio,fecha from citas order by fecha";
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			while (rs.next()) {
				Cita vo = new Cita();		
				vo.setIdCita(rs.getString("id_cita"));
				vo.setIdCliente(rs.getString("id_cliente"));
				vo.setIdPaciente(rs.getString("id_paciente"));
				vo.setIdServicio(rs.getString("id_servicio"));
				vo.setCantServicio(rs.getString("cant_servicio"));
				vo.setFecha(rs.getString("fecha"));
				c.add(vo);
			}

		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return c;
	}

}
